package com.office.web;

import com.office.bean.TClass;
import com.office.bean.TStudent;
import com.office.service.StudentService;
import com.office.utils.DepartBeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class StudentController {
    @Autowired
    StudentService studentService;

    @RequestMapping("/student_list")
    public String studnetlist(int pageIndex, int pagesize, String name, @RequestParam(defaultValue = "0") int classId, Model model){
        HashMap<String, Object> map = new HashMap<>();

        System.out.println("前端传过来的数据："+"   pageindex:  "+pageIndex+"   name  :"+name+"classid"+ classId);

        //放入前端传递过来的数据
        map.put("pageIndex",pageIndex);
        map.put("pagesize",pagesize);
        map.put("name",name);
        map.put("classId",classId);

        //查询总记录数
        int count = studentService.findallcount(map);
        //查询分页数据
        List<TStudent> studentrecods = studentService.findstudentbypage(map);
        DepartBeanUtils<TStudent> studentUtils = new DepartBeanUtils<>(pageIndex,pagesize,count,studentrecods);//打包为工具类
        model.addAttribute("studentutils",studentUtils);//回显的数据

        //回显数据和班级
        model.addAttribute("name",name);
        model.addAttribute("classid",classId);

        return "studentlist";
    }

    @RequestMapping("/student_findclass")
    @ResponseBody
    public List<TClass> findclass(){
        List<TClass> findclass = studentService.findclass();
        System.out.println(findclass);
        return  findclass;
    }

    //带出excell
    @RequestMapping("/student_exportexcell")
    private void export(String name, @RequestParam(defaultValue = "0")int classId, HttpServletResponse resp) throws IOException {

        resp.setContentType("text/html;charset=utf-8");
        Map<String, Object> map = new HashMap<>();
        map.put("name",name);
        map.put("classId",classId);
        System.out.println("导出的excell文件，前端传入的值"+"name"+name+"classId"+classId);
        List<TStudent> studentlist = studentService.exportexcell(map);

        //1.创建一个excell的文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("学生信息");

        String[] titles = {"编号", "工号", "姓名", "性别", "生日", "学校"};
        HSSFRow row = sheet.createRow(0);
        for (int i=0;i<titles.length;i++){
            HSSFCell cell = row.createCell(i);//创建excell 列（每个单元格）
            cell.setCellValue(titles[i]);
        }
        for (int i=0;i<studentlist.size();){
            TStudent s = studentlist.get(i);
            System.out.println(s);
            HSSFRow rowtype = sheet.createRow(++i);//创建excell的  行

            HSSFCell idcell = rowtype.createCell(0);
            idcell.setCellValue(s.getId());//赋值

            HSSFCell nocell = rowtype.createCell(1);
            nocell.setCellValue(s.getNo());

            HSSFCell namecell = rowtype.createCell(2);
            namecell.setCellValue(s.getName());

            HSSFCell sexcell = rowtype.createCell(3);
            sexcell.setCellValue(s.getSex());

            HSSFCell birthdaycell = rowtype.createCell(4);
            birthdaycell.setCellValue(s.getBirthday());

            HSSFCell schoolcell = rowtype.createCell(5);
            schoolcell.setCellValue(s.getSchool());
        }

        //字节输出流,下载
        ServletOutputStream outputStream = resp.getOutputStream();
        String fillname = "学生信息表.xls";

        // 中文需要编码
        resp.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fillname, "utf-8"));

        workbook.write(outputStream);
    }

    //导入表
    @RequestMapping("/student_import")
    public void studentimport(MultipartFile file,HttpServletResponse resp) throws IOException {
        resp.setContentType("text/html;charset=utf-8");
        InputStream inputStream = file.getInputStream();
        //创建一个工作对象
        HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
        //获取工作表总共有多少行
        HSSFSheet sheetAt = sheets.getSheetAt(0);

        List<TStudent> students=new ArrayList<>();
        for (int i = 1; i <= sheetAt.getLastRowNum(); i++) {
            HSSFRow row = sheetAt.getRow(i);//得到每一行
            if (row == null) {// 如果为空，不处理
                continue;
            }
            // 循环遍历单元格
            String cellStr="";
            TStudent student=new TStudent();
            for(int j=0;j<row.getLastCellNum();j++){
                HSSFCell cell = row.getCell(j);
                if (cell == null) {// 单元格为空设置cellStr为空串
                    cellStr = "";
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理
                    cellStr = String.valueOf(cell.getBooleanCellValue());
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理
                    cellStr = cell.getNumericCellValue() + "";
                } else {// 其余按照字符串处理
                    cellStr = cell.getStringCellValue();
                }
                if(j==0){
                    student.setNo(cellStr);
                }else if(j==1){
                    student.setName(cellStr);
                }else if(j==2){
                    student.setClassId(Integer.valueOf(cellStr));
                }else if(j==3){
                    student.setSex(cellStr);
                }else if(j==4){
                    student.setPhone(cellStr);
                }else if(j==5){
                    student.setQq(cellStr);
                }else if(j==6){
                    student.setEmail(cellStr);
                }else if(j==7){
                    student.setCardno(cellStr);
                }else if(j==8){
                    student.setSchool(cellStr);
                }else if(j==9){
                    student.setEducation(cellStr);
                }else if(j==10){
                    student.setBirthday(cellStr);
                }else if(j==11){
                    student.setCreatedate(cellStr);
                }
            }
            students.add(student);
        }
        for (TStudent student : students) {
            System.out.println(student);
            //把集合中的对象新增到数据库
            System.out.println("把集合中的对象新增到数据库.....................");

        }
        resp.getWriter().write("<script>alert('学生导入成功');location.href='/student_list?pageIndex=1&pageSize=5';</script>");
    }
}
